import pandas as pd
customers = pd.read_excel("data/bikeshops.xlsx", sheet=1)
products = pd.read_excel("data/bikes.xlsx", sheet=1)
orders = pd.read_excel("data/orders.xlsx", sheet=1)

df = pd.merge(orders, customers, left_on="customer.id", right_on="bikeshop.id")

df = pd.merge(df, products, left_on="product.id", right_on="bike.id")

df = df.T.drop_duplicates().T

df["price.extended"] = df["price"] * df["quantity"]

df = df[["order.date", "order.id", "order.line", "bikeshop.name", "model",
         "quantity", "price", "price.extended", "category1", "category2", "frame"]]

df = df.sort_values(["order.id","order.line"])

df = df.fillna(value=0)

df = df.reset_index(drop=True)

## You can easily melt which seems to be anothre

 #melt()

## I think melt reverses the pivot_table. 
## summarise in R is arg, spread is pivot_table/melt

df["price"] = pd.qcut(df["price"],2)

merger = df.copy()

df = df.groupby(["bikeshop.name", "model", "category1", "category2", "frame", "price"]).agg({"quantity":"sum"}).reset_index().pivot_table(index="model", columns="bikeshop.name",values="quantity").reset_index().reset_index(drop=True)

df.head()

rad = pd.merge(df, merger.drop_duplicates("model"), on="model", how="left")

rad.price.extended = rad.price

rad = rad.drop(["order.date","order.id","order.line","bikeshop.name","quantity","price.extended"],axis=1)

non_cat = list(rad.select_dtypes(exclude=["category","object"]).columns)

cat = list(rad.select_dtypes(include=["category","object"]).columns)

rad[non_cat] = rad[non_cat].fillna(value=0)

rad[rad.columns.difference(cat)] = rad[rad.columns.difference(cat)]/rad[rad.columns.difference(cat)].sum()

%matplotlib inline
import matplotlib.cm as cm
import seaborn as sn
from sklearn.cluster import KMeans
cmap = sn.cubehelix_palette(as_cmap=True, rot=-.3, light=1)
sn.clustermap(rad.iloc[:,1:-4:].T.head(), cmap=cmap, linewidths=.5)

cluster_range = range(1, 8)
cluster_errors = []

for num_clusters in cluster_range:
    clusters = KMeans( num_clusters )
    clusters.fit(rad.iloc[:,1:-4:].T)
    cluster_errors.append( clusters.inertia_ )

clusters_df = pd.DataFrame( { "num_clusters":cluster_range, "cluster_errors": cluster_errors } )
clusters_df

import matplotlib.pyplot as plt

plt.figure(figsize=(12,6))
plt.plot( clusters_df.num_clusters, clusters_df.cluster_errors, marker = "o" )

from sklearn.metrics import silhouette_samples, silhouette_score
import numpy as np

cluster_range = range( 2, 7 )

for n_clusters in cluster_range:
    # Create a subplot with 1 row and 2 columns
    fig, (ax1, ax2) = plt.subplots(1, 2)
    fig.set_size_inches(18, 7)

    # The 1st subplot is the silhouette plot
    # The silhouette coefficient can range from -1, 1 but in this example all
    # lie within [-0.1, 1]
    ax1.set_xlim([-0.1, 1])
    # The (n_clusters+1)*10 is for inserting blank space between silhouette
    # plots of individual clusters, to demarcate them clearly.
    ax1.set_ylim([0, len(rad.iloc[:,1:-4:]) + (n_clusters + 1) * 10])

    # Initialize the clusterer with n_clusters value and a random generator
    # seed of 10 for reproducibility.
    clusterer = KMeans(n_clusters=n_clusters, random_state=10)
    cluster_labels = clusterer.fit_predict( rad.iloc[:,1:-4:] )

    # The silhouette_score gives the average value for all the samples.
    # This gives a perspective into the density and separation of the formed
    # clusters
    silhouette_avg = silhouette_score(rad.iloc[:,1:-4:], cluster_labels)
    print("For n_clusters =", n_clusters,
        "The average silhouette_score is :", silhouette_avg)

    # Compute the silhouette scores for each sample
    sample_silhouette_values = silhouette_samples(rad.iloc[:,1:-4:], cluster_labels)

    y_lower = 10
    for i in range(n_clusters):
      # Aggregate the silhouette scores for samples belonging to
      # cluster i, and sort them
      ith_cluster_silhouette_values = \
          sample_silhouette_values[cluster_labels == i]

      ith_cluster_silhouette_values.sort()

      size_cluster_i = ith_cluster_silhouette_values.shape[0]
      y_upper = y_lower + size_cluster_i

      color = cm.spectral(float(i) / n_clusters)
      ax1.fill_betweenx(np.arange(y_lower, y_upper),
                        0, ith_cluster_silhouette_values,
                        facecolor=color, edgecolor=color, alpha=0.7)

      # Label the silhouette plots with their cluster numbers at the middle
      ax1.text(-0.05, y_lower + 0.5 * size_cluster_i, str(i))

      # Compute the new y_lower for next plot
      y_lower = y_upper + 10  # 10 for the 0 samples

    ax1.set_title("The silhouette plot for the various clusters.")
    ax1.set_xlabel("The silhouette coefficient values")
    ax1.set_ylabel("Cluster label")

    # The vertical line for average silhoutte score of all the values
    ax1.axvline(x=silhouette_avg, color="red", linestyle="--")

    ax1.set_yticks([])  # Clear the yaxis labels / ticks
    ax1.set_xticks([-0.1, 0, 0.2, 0.4, 0.6, 0.8, 1])

    # 2nd Plot showing the actual clusters formed
    colors = cm.spectral(cluster_labels.astype(float) / n_clusters)
    ax2.scatter(rad.iloc[:, -2], rad.iloc[:, -1], marker='.', s=30, lw=0, alpha=0.7,
              c=colors)

    # Labeling the clusters
    centers = clusterer.cluster_centers_
    # Draw white circles at cluster centers
    ax2.scatter(centers[:, 0], centers[:, 1],
              marker='o', c="white", alpha=1, s=200)

    for i, c in enumerate(centers):
      ax2.scatter(c[0], c[1], marker='$%d$' % i, alpha=1, s=50)

    ax2.set_title("The visualization of the clustered data.")
    ax2.set_xlabel("Feature space for the 1st feature")
    ax2.set_ylabel("Feature space for the 2nd feature")

    plt.suptitle(("Silhouette analysis for KMeans clustering on sample data "
                "with n_clusters = %d" % n_clusters),
               fontsize=14, fontweight='bold')

    plt.show()

## Start the clusters here.

clusters = KMeans(4)
clusters.fit(rad.iloc[:,1:-4:].T)
#cluster_errors.append( clusters.inertia_ )
centroids = clusters.cluster_centers_

clusters = KMeans(4)
clusters.fit(rad.iloc[:,1:-4:])
labels = clusters.predict(rad.iloc[:,1:-4:])
# Centroid values

labels

centroids.shape

## IF I had to traspose, I could've sworns

rad.iloc[:,1:-4:].shape

rad.head()

for i, c in enumerate(centroids):
    rad["Cluster "+str(i)] = list(c)

rad_final = rad.drop(list(rad.iloc[:,1:].iloc[:,:-8].columns),axis=1)

rad_final.sort_values("Cluster 0").head(10)

rad_final = rad_final.rename(columns={"Cluster 0":"Low End Road Bike Customer"})

rad_final.sort_values("Cluster 1").head(10)

rad_final = rad_final.rename(columns={"Cluster 1":"High End Road Bike Customer"})

rad_final.sort_values("Cluster 2").head(10)

rad_final = rad_final.rename(columns={"Cluster 2":"Aluminum Mountain Bike Customers"})

rad_final.sort_values("Cluster 3").head(10)

rad_final = rad_final.rename(columns={"Cluster 3":"High End Carbon Mountain Bike Customers"})

from sklearn.pipeline import make_pipeline

import seaborn as sns
from sklearn.decomposition import PCA
from sklearn.preprocessing import StandardScaler
#pca2 = PCA(n_components=2)

pca2_results = make_pipeline(StandardScaler(),PCA(n_components=2)).fit_transform(rad.iloc[:,1:-(4+len(centroids))])

for i in range(pca2_results.shape[1]):
    rad.iloc[:,1:-(4+len(centroids)):]["pca_"+str(i)] = pca2_results[:,i]

cmap = sns.cubehelix_palette(as_cmap=True)
f, ax = plt.subplots(figsize=(20,15))
points = ax.scatter(pca2_results[:,0], pca2_results[:,1],c=labels,  s=50, cmap=cmap)
#c=df_2.TARGET,
f.colorbar(points)
plt.show()
### Each dot is a cycle shop

from sklearn.metrics.pairwise import cosine_similarity

import numpy as np

cosine_similarity(rad.iloc[:,1:-(4+len(centroids))].T).shape

cos_mat = pd.DataFrame(cosine_similarity(rad.iloc[:,1:-(4+len(centroids))].T), index=list(rad.iloc[:,1:-(4+len(centroids))].columns), columns=list(rad.iloc[:,1:-(4+len(centroids))].columns))

## Make diagonal zero
cos_mat.values[[np.arange(len(cos_mat))]*2] = 0

def front(self, n):
    return self.iloc[:, :n]

pd.DataFrame.front = front

cos_mat.head(5).front(5)

edgeLimit = 0.7
cos_mat = cos_mat.applymap(lambda x: 0 if x <edgeLimit else x)

cos_mat.head(5).front(5)

import igraph

from scipy.cluster.hierarchy import dendrogram, linkage

## I think the diagonal creates the one drpop
Z = linkage(cos_mat, 'ward')

cos_mat.drop_duplicates().shape

from scipy.cluster.hierarchy import cophenet
from scipy.spatial.distance import pdist

c, coph_dists = cophenet(Z, pdist(cos_mat))

#list(cos_mat.as_matrix())

cos_mat

Z.shape

plt.figure(figsize=(25, 10))
plt.title('Hierarchical Clustering Dendrogram')
plt.xlabel('sample index')
plt.ylabel('distance')
dendrogram(
    Z,
    leaf_rotation=90.,  # rotates the x axis labels
    leaf_font_size=8.,  # font size for the x axis labels
    labels = list(cos_mat.columns),
)
plt.show()

## You can create a reasonable argument for 4 - 6 clusters. 

cos_mat["Detroit Cycles"].sort_values(ascending=False)

#### Supposedly, you can do some network analysis here.  Of course, you are not sure how. 

cos_igraph = igraph.Graph.Adjacency(list(cos_mat.as_matrix()),mode = 'undirected')

cos_bet = igraph.Graph.edge_betweenness(cos_igraph)

cos_bet

plt.title('Hierarchical Clustering Dendrogram')
plot_dendrogram(cos_igraph)
plt.show()

## Possibly use this for future network:
https://python-graph-gallery.com/327-network-from-correlation-matrix/